package com.example.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.example.listener.MyReadListener;
import com.opencsv.bean.CsvToBean;
import com.opencsv.bean.CsvToBeanBuilder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.nio.charset.Charset;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.List;

@Slf4j
public class ExcelUtils {
    public static <T> List<T> readExcel(String path,Class<T> cla) {
        if(!Files.exists(Paths.get(path))){
            System.out.println("文件不存在!!!");
            return null;
        }
        //读取文件
        File file=new File(path);
        MyReadListener myReadListener=new MyReadListener<T>();
        EasyExcel.read(file, cla,myReadListener)
                .sheet()
                .headRowNumber(1)
                .doRead();
        return myReadListener.getData();

    }

    public static <T> void writeExcel(String path,List<T> data,Class<T> cla) {
        //去除文件后缀
        String exportPrefix = path.contains(".") ? path.substring(0, path.indexOf(".")) : path;
        String exportPath=path;
        //判断输出文件是否存在
        for(int i=1;;i++){
            if(!Files.exists(Paths.get(exportPath))){
                break;
            }
            exportPath=exportPrefix+"副本"+i+".xlsx";
        }
        File file=new File(exportPath);
        // 自定义处理器设置字体样式
        CellWriteHandler cellWriteHandler=new CellWriteHandler() {
            boolean once=true;
            CellStyle cellStyle;
            @Override
            public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
                Workbook workbook=writeSheetHolder.getSheet().getWorkbook();
                if(once){
                    Font font = workbook.createFont();
                    font.setFontName("宋体");
                    font.setFontHeightInPoints((short) 11);
                    cellStyle=workbook.createCellStyle();
                    cellStyle.setFont(font);
                    once=false;
                }
                cell.setCellStyle(cellStyle);
            }
            @Override
            public int order(){
                return 50001;
            }
        };
        try {
            //通过反射设置单元格最大字符数为Integer.MAX_VALUE
            SpreadsheetVersion excel2007 = SpreadsheetVersion.EXCEL2007;
            Field field = excel2007.getClass().getDeclaredField("_maxTextLength");
            field.setAccessible(true);//设置可访问，就可以访问private数据
            field.set(excel2007,Integer.MAX_VALUE);
            //将excel数据写出
            EasyExcel.write(file, cla)
                    .sheet("sheet1")
                    .registerWriteHandler(cellWriteHandler)
                    .doWrite(data);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static Workbook readWorkbook(String path){
        try {
            if(!Files.exists(Paths.get(path))){
                System.out.println("文件不存在!!!");
                return null;
            }
            FileInputStream fileInputStream=new FileInputStream(new File(path));
            Workbook workbook=new XSSFWorkbook(fileInputStream);
            //关闭流
            fileInputStream.close();
            //返回结果
            return workbook;

        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }

    }

    public static void writeWorkbook(String path,Workbook workbook){
        try {
            //去除文件后缀
            String filePrefix = path.contains(".") ? path.substring(0, path.indexOf(".")) : path;
            String filePath=path;
            //确保生成的文件不存在，防止文件被覆盖
            for(int i=1;;i++){
                if(!Files.exists(Paths.get(filePath))){
                    break;
                }
                filePath=filePrefix+"副本"+i+".xlsx";
            }
            //设置excel字体
            Font font = workbook.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 11);
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFont(font);
            for(int sheetIndex=0;sheetIndex<workbook.getNumberOfSheets();sheetIndex++){
                Sheet sheet = workbook.getSheetAt(sheetIndex);
                for (Row row : sheet) {
                    for (Cell cell : row) {
                        cell.setCellStyle(cellStyle);
                    }
                }
            }
            //写出数据
            File file=new File(filePath);
            FileOutputStream fileOutputStream=new FileOutputStream(file);
            workbook.write(fileOutputStream);
            //关闭流
            workbook.close();
            fileOutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    public static <T> List<T> readCSV(File file,Class<T> cla,int startLine){
        if(!file.exists()){
            System.out.println("文件不存在！");
            return null;
        }
        try {
            FileReader fileReader = new FileReader(file, Charset.forName("GBK"));
            BufferedReader bufferedReader = new BufferedReader(fileReader);
            CsvToBean<T> csvToBean = new CsvToBeanBuilder<T>(bufferedReader).withType(cla)  //映射的类型
                    .withQuoteChar((char) 0)   //设置引用字符为空，防止出现解析时引用字符（默认为双引号）不成对错误
                    .withIgnoreLeadingWhiteSpace(true)  //忽略CSV文件每行开头的空白行
                    .withSkipLines(startLine)  //从第几行开始读，一般从0行，要读表头
                    .withIgnoreEmptyLine(true)  //忽略空白行
                    .build();
            List<T> parseList = csvToBean.parse();
            return parseList;
        } catch (Exception e) {
            e.printStackTrace();
            return null; //可能情况 反斜杠造成头部不匹配
        }
    }
}
